/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.modules.biz.repository;

import me.zhengjie.modules.biz.domain.HazardousChemicalsTotal;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

/**
* @website https://eladmin.vip
* @author Admin
* @date 2023-03-10
**/
public interface HazardousChemicalsTotalRepository extends JpaRepository<HazardousChemicalsTotal, Integer>, JpaSpecificationExecutor<HazardousChemicalsTotal> {

    @Query(value = "select * from hj_hazardous_chemicals_total where  name = ?1 and amount = ?2 and lab_id = ?3 and cabinet_id = ?4 and type =?5 and count_type = ?6",nativeQuery = true)
    HazardousChemicalsTotal findByNameAmountLabCabinet(String name,Float amount,Integer lab_id,Integer cabinet_id,Integer type,Integer count_type);

    @Query(value = "select * from hj_hazardous_chemicals_total where  name = ?1 and category = ?2 and storage = ?3  and type = 1",nativeQuery = true)
    HazardousChemicalsTotal findEquipmentByNameAndCategoryAndStorage(String name, String category, int storage);


    @Query(value = "select sum(total_number) from hj_hazardous_chemicals_total where name = ?1 and  type = ?2",nativeQuery = true)
    Float sumTotalNum(String name , Integer type);

    @Query(value = "select sum(total_number) from hj_hazardous_chemicals_total where name = ?1 and amount = ?2 and cabinet_id = ?3 and count_type = ?4",nativeQuery = true)
    Float sumTotalNumCabinetId(String name , Float amount,int cabinetId,int countType);

    @Query(value = "select sum(total_number) from hj_hazardous_chemicals_total where name = ?1 and amount = ?2 and lab_id like ?3 and count_type = ?4",nativeQuery = true)
    Float sumTotalNumLabId(String name , Float amount,int labId ,int countType);

    @Query(value = "select sum(total_number) from hj_hazardous_chemicals_total where name = ?1 and amount = ?2 and lab_id = ?3 and cabinet_id=?4 and count_type = ?5",nativeQuery = true)
    Float sumTotalNumLabIdCabinetId(String name , Float amount,int labId,int cabinetId,int countType);
    @Query(value = "SELECT t.* FROM hj_hazardous_chemicals_total t where t.type = ?1 GROUP BY name order by t.id desc limit ?2 , ?3",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByNull(int type ,int page,int size);

    @Query(value = "SELECT t.* FROM hj_hazardous_chemicals_total t left join (select * from hanjiang.hj_hot_hazardous where user_id = ?4) h on t.id = h.hc_id where t.type = ?1 GROUP BY name order by h.count desc  limit ?2 , ?3",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByHot(int type ,int page,int size, long userId);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total GROUP BY name limit ?1 , ?2",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByNullNotType(int page,int size);


    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE type = ?1 and name like ?2 GROUP BY name order by id desc limit ?3,?4",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByName(int type,String name,int page,int size);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE name like ?1 GROUP BY name limit ?2,?3",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByNameNotType(String name,int realPage, int realSize);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE type = ?1 and lab_id = ?2 GROUP BY name,lab_id order by id desc limit ?3,?4",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByLabId(int type,int labId,int page,int size);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE type=?1 and cabinet_id = ?2 GROUP BY name,cabinet_id order by id desc limit ?3,?4",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByCabinetId(int type,int cabinetId,int page,int size);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE cabinet_id in ?1 GROUP BY name,cabinet_id limit ?2,?3",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByCabinetINotType(int[] cabinetIds,int page,int size);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE type=?1 and name like ?2 AND lab_id = ?3 GROUP BY name,lab_id order by id desc limit ?4,?5",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByNameLabid(int type,String name, int labId, int real_page, int real_size);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE type=?1 and lab_id = ?2 AND cabinet_id = ?3 GROUP BY name,lab_id,cabinet_id,count_type order by id desc limit ?4,?5",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByLabIdcabinetId(int type,int labId, int cabinetId, int real_page, int real_size);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE type=?1 and name like ?2 AND cabinet_id = ?3 GROUP BY name,cabinet_id,count_type order by id desc limit ?4,?5",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByNameLcabinetId(int type,String name, int cabinetId, int real_page, int real_size);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE name like ?1 AND cabinet_id in ?2 GROUP BY name,cabinet_id,count_type limit ?3,?4",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByNameAndCabinetIdNotType(String name, int[] cabinetId, int realPage, int realSize);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE type =?1 and name like ?2 AND lab_id = ?3 AND cabinet_id = ?4 GROUP BY name,lab_id,cabinet_id order by id desc limit ?5,?6",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByNameLabIdLcabinetId(int type,String name, int labId, int cabinetId, int real_page, int real_size);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total GROUP BY name",nativeQuery = true)
    List<HazardousChemicalsTotal> countNoLimit();

    @Query(value = "SELECT type FROM hj_hazardous_chemicals_total WHERE name = ?1 LIMIT 0,1",nativeQuery = true)
    Integer getTypeByName(String name);

    @Query(value = "select sum(total_number) from hj_hazardous_chemicals_total where name = ?1",nativeQuery = true)
    float sumTotalNumName(String name);

    @Query(value = "select * from hj_hazardous_chemicals_total where name = ?1 and type = ?2 limit 0,1",nativeQuery = true)
    HazardousChemicalsTotal findByNameAndType(String name, Integer type);

    @Query(value = "SELECT * from hj_hazardous_chemicals_total where name = ?1 and amount = ?2 limit 0,1",nativeQuery = true)
    HazardousChemicalsTotal findIdByNameAmount(String name, Float amount);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total hct,hj_project_hazardous_chemicals phc WHERE phc.project_id= ?1 and hct.name in (phc.name) and hct.type = phc.type",nativeQuery = true)
    List<HazardousChemicalsTotal> findByIds(Integer id);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total GROUP BY name,amount",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByNullList();

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE type = ?1 and category = ?2 GROUP BY name order by id desc limit ?3,?4",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByCategory(int type, String category, int real_page, int real_size);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE type = ?1 and name like ?2 and category = ?3 GROUP BY name order by id desc limit ?4,?5",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByNameCategory(int type, String name, String category,int real_page, int real_size);

    HazardousChemicalsTotal findByNameAndBatchNumber(String name, String batchNumber);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total where type = ?1 GROUP BY name,batch_number order by id desc limit ?2 , ?3",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByNullAndBatchNumber(int type ,int page,int size);

    @Query(value = "SELECT name \"标准物质名称\", number \"标准物质编号\", batch_number \"批号\", ingredient_data \"成分/名称\", sum(total_number) \"数量\", expiration_date \"有效期\", unit \"单位\" FROM hj_hazardous_chemicals_total where type = 2 GROUP BY name,batch_number",nativeQuery = true)
    List<Map<String, Object>> queryAllForDownload();

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE type = ?1 and name like ?2 GROUP BY name,batch_number order by id desc limit ?3,?4",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByNameAndBatchNumber(int type,String name,int page,int size);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE type = ?1 and lab_id = ?2 GROUP BY name,lab_id,batch_number order by id desc limit ?3,?4",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByLabIdAndBatchNumber(int type,int labId,int page,int size);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE type=?1 and cabinet_id = ?2 GROUP BY name,cabinet_id,batch_number order by id desc limit ?3,?4",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByCabinetIdAndBatchNumber(int type,int cabinetId,int page,int size);

    @Query(value = "SELECT name \"标准物质名称\", number \"标准物质编号\", batch_number \"批号\", ingredient_data \"成分/名称\", sum(total_number) \"数量\", expiration_date \"有效期\", unit \"单位\" FROM hj_hazardous_chemicals_total WHERE type = 2 and cabinet_id = ?1 GROUP BY name,cabinet_id,batch_number",nativeQuery = true)
    List<Map<String, Object>> queryForDownloadByCabinetId(int cabinetId);

    @Query(value = "SELECT name \"标准物质名称\", number \"标准物质编号\", batch_number \"批号\", ingredient_data \"成分/名称\", sum(total_number) \"数量\", expiration_date \"有效期\", unit \"单位\" FROM hj_hazardous_chemicals_total WHERE type = 2 and name = ?1 GROUP BY name,cabinet_id,batch_number",nativeQuery = true)
    List<Map<String, Object>> queryForDownloadByName(String name);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE type=?1 and name like ?2 AND lab_id = ?3 GROUP BY name,lab_id,batch_number order by id desc limit ?4,?5",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByNameLabidAndBatchNumber(int type,String name, int labId, int real_page, int real_size);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE type=?1 and lab_id = ?2 AND cabinet_id = ?3 GROUP BY name,lab_id,cabinet_id,count_type,batch_number order by id desc limit ?4,?5",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByLabIdcabinetIdAndBatchNumber(int type,int labId, int cabinetId, int real_page, int real_size);

    @Query(value = "SELECT name \"标准物质名称\", number \"标准物质编号\", batch_number \"批号\", ingredient_data \"成分/名称\", sum(total_number) \"数量\", expiration_date \"有效期\", unit \"单位\" FROM hj_hazardous_chemicals_total WHERE type=2 AND cabinet_id = ?1 and name = ?2 GROUP BY name,cabinet_id,batch_number",nativeQuery = true)
    List<Map<String, Object>> queryForDownloadByCabinetIdAndName( int cabinetId, String name);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE type=?1 and name like ?2 AND cabinet_id = ?3 GROUP BY name,cabinet_id,count_type,batch_number order by id desc limit ?4,?5",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByNameLcabinetIdAndBatchNumber(int type,String name, int cabinetId, int real_page, int real_size);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE type =?1 and name like ?2 AND lab_id = ?3 AND cabinet_id = ?4 GROUP BY name,lab_id,cabinet_id,batch_number order by id desc limit ?5,?6",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByNameLabIdLcabinetIdAndBatchNumber(int type,String name, int labId, int cabinetId, int real_page, int real_size);


    @Query(value = "select sum(total_number) from hj_hazardous_chemicals_total where name = ?1 and  batch_number = ?2",nativeQuery = true)
    Float sumTotalNumAndBatchNumber(String name , String batchNumber);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE name = ?1 limit 0,1",nativeQuery = true)
    HazardousChemicalsTotal queryAllByName(String name);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE created_at between ?1 AND ?2 GROUP BY name limit ?3 , ?4",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByTimeNotType(Timestamp startTime, Timestamp endTime, int realPage, int realSize);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE name like ?1 AND created_at between ?2 AND ?3 GROUP BY name limit ?4 , ?5",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByNameAndTimeNotType(String name, Timestamp ts, Timestamp te, int realPage, int realSize);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE cabinetId in ?1 created_at between ?2 AND ?3 GROUP BY name limit ?4 , ?5",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByCabinetIdAndTimeNotType(int[] cabinetId, Timestamp ts, Timestamp te, int realPage, int realSize);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total WHERE name like ?1 AND cabinetId in ?2 created_at between ?3 AND ?4 GROUP BY name limit ?5 , ?6",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllByNameAndCabinetIdAndTimeNotType(String name, int[] cabinetId, Timestamp ts, Timestamp te, int realPage, int realSize);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total where type = ?1 and is_apply = 0  GROUP BY name limit ?2 , ?3",nativeQuery = true)
    List<HazardousChemicalsTotal> queryAllForMachine(int type ,int page,int size);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total hct,hj_project_hazardous_chemicals phc WHERE phc.project_id= ?1 and hct.id in (phc.hct_id) and hct.is_audit = 0",nativeQuery = true)
    List<HazardousChemicalsTotal> findByIdsNotApply(Integer id);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals_total hct,hj_project_hazardous_chemicals phc WHERE phc.project_id= ?1 and hct.id in (phc.hct_id) and hct.is_audit = 0 and hct.cabinet_id in ?2",nativeQuery = true)
    List<HazardousChemicalsTotal> findByIdsNotApplyTypeCabinet(int id, int[] cabinetIds);

    @Query(value = "select * from hj_hazardous_chemicals_total where  name = ?1 and lab_id = ?2 and cabinet_id = ?3 and type = 3",nativeQuery = true)
    HazardousChemicalsTotal findHazardousChemicalByNameAndLabAndCabinet(String name, Integer labId, Integer cabinetId);

    HazardousChemicalsTotal findByNameAndGradeAndLabIdAndCabinetIdAndDangerClassesAndExpirationDate(String name, String grade, Integer labId, Integer cabinetId, Integer dangerClasses, Timestamp expirationDate);

    HazardousChemicalsTotal findByNameAndCategoryAndNumberAndBatchNumberAndIngredientDataAndMatrixAndUnitAndCabinetIdAndExpirationDate(String name, String category, String number, String batchNumber, String ingredientData, String matrix, int unit, Integer cabinetId, Timestamp expirationDate);

    HazardousChemicalsTotal findByNameAndBatchNumberAndType(String name, String batchNumber, Integer type);

    HazardousChemicalsTotal findByNameAndCategoryAndTypeAndStorage(String name, String category, Integer type, int storage);

    HazardousChemicalsTotal findByNameAndCabinetIdAndType(String name, Integer cabinetId, Integer type);
}
